package src.chapter35;

import java.sql.*;

public class ScrollUpdateResultSet {
    public static void main(String[] args)
            throws SQLException, ClassNotFoundException {
        // Load the JDBC driver
        Class.forName("oracle.jdbc.driver.OracleDriver");
        System.out.println("Driver loaded");

        // Connect to a database
        Connection connection = DriverManager.getConnection
                ("jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl",
                        "scott", "tiger");
        connection.setAutoCommit(true);
        System.out.println("Database connected");

        // Get a new statement for the current connection
        Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

        // Get ResultSet
        ResultSet resultSet = statement.executeQuery
                ("select state, capital from StateCapital");

        System.out.println("Before update ");
        displayResultSet(resultSet);

        // Update the second row
        resultSet.absolute(2); // Move cursor to the second row
        resultSet.updateString("state", "New S"); // Update the column
        resultSet.updateString("capital", "New C"); // Update the column
        resultSet.updateRow(); // Update the row in the data source

        // Insert after the last row
        resultSet.last();
        resultSet.moveToInsertRow(); // Move cursor to the insert row
        resultSet.updateString("state", "Florida");
        resultSet.updateString("capital", "Tallahassee");
        resultSet.insertRow(); // Insert the row
        resultSet.moveToCurrentRow(); // Move the cursor to the current row

        // Delete fourth row
        resultSet.absolute(4); // Move cursor to the 5th row
        resultSet.deleteRow(); // Delete the second row

        System.out.println("After update ");
        resultSet = statement.executeQuery
                ("select state, capital from StateCapital");
        displayResultSet(resultSet);

        // Close the connection
        resultSet.close();
    }

    private static void displayResultSet(ResultSet resultSet)
            throws SQLException {
        ResultSetMetaData rsMetaData = resultSet.getMetaData();
        resultSet.beforeFirst();
        while (resultSet.next()) {
            for (int i = 1; i <= rsMetaData.getColumnCount(); i++)
                System.out.printf("%-12s\t", resultSet.getObject(i));
            System.out.println();
        }
    }
} 
